{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SUM and COUNT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Total world population\n",
    "\n",
    "Show the total population of the world.\n",
    "\n",
    "```\n",
    "world(name, continent, area, population, gdp)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "world <- dbReadTable(con, 'world')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>popl</th></tr>\n",
       "\t<tr><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>7118632738</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " popl\\\\\n",
       " <dbl>\\\\\n",
       "\\hline\n",
       "\t 7118632738\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| popl &lt;dbl&gt; |\n",
       "|---|\n",
       "| 7118632738 |\n",
       "\n"
      ],
      "text/plain": [
       "  popl      \n",
       "1 7118632738"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>%\n",
    "    summarise(popl=sum(population))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. List of continents\n",
    "\n",
    "List all the continents - just once each."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 8 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>continent</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Asia         </td></tr>\n",
       "\t<tr><td>Europe       </td></tr>\n",
       "\t<tr><td>Africa       </td></tr>\n",
       "\t<tr><td>Caribbean    </td></tr>\n",
       "\t<tr><td>South America</td></tr>\n",
       "\t<tr><td>Eurasia      </td></tr>\n",
       "\t<tr><td>Oceania      </td></tr>\n",
       "\t<tr><td>North America</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 8 × 1\n",
       "\\begin{tabular}{l}\n",
       " continent\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Asia         \\\\\n",
       "\t Europe       \\\\\n",
       "\t Africa       \\\\\n",
       "\t Caribbean    \\\\\n",
       "\t South America\\\\\n",
       "\t Eurasia      \\\\\n",
       "\t Oceania      \\\\\n",
       "\t North America\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 8 × 1\n",
       "\n",
       "| continent &lt;chr&gt; |\n",
       "|---|\n",
       "| Asia          |\n",
       "| Europe        |\n",
       "| Africa        |\n",
       "| Caribbean     |\n",
       "| South America |\n",
       "| Eurasia       |\n",
       "| Oceania       |\n",
       "| North America |\n",
       "\n"
      ],
      "text/plain": [
       "  continent    \n",
       "1 Asia         \n",
       "2 Europe       \n",
       "3 Africa       \n",
       "4 Caribbean    \n",
       "5 South America\n",
       "6 Eurasia      \n",
       "7 Oceania      \n",
       "8 North America"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    distinct(continent)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. GDP of Africa\n",
    "\n",
    "Give the total GDP of Africa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>gdp</th></tr>\n",
       "\t<tr><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1.811788e+12</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " gdp\\\\\n",
       " <dbl>\\\\\n",
       "\\hline\n",
       "\t 1.811788e+12\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| gdp &lt;dbl&gt; |\n",
       "|---|\n",
       "| 1.811788e+12 |\n",
       "\n"
      ],
      "text/plain": [
       "  gdp         \n",
       "1 1.811788e+12"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>%\n",
    "    filter(continent=='Africa') %>% \n",
    "    summarise(gdp=sum(gdp, na.rm=TRUE))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Count the big countries\n",
    "\n",
    "How many countries have an **area** of at least 1000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>n</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>29</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " n\\\\\n",
       " <int>\\\\\n",
       "\\hline\n",
       "\t 29\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| n &lt;int&gt; |\n",
       "|---|\n",
       "| 29 |\n",
       "\n"
      ],
      "text/plain": [
       "  n \n",
       "1 29"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(area>=1e6) %>% \n",
    "    summarise(n=n())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Baltic states population\n",
    "\n",
    "What is the total **population** of ('Estonia', 'Latvia', 'Lithuania')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>popl</th></tr>\n",
       "\t<tr><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>6251750</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " popl\\\\\n",
       " <dbl>\\\\\n",
       "\\hline\n",
       "\t 6251750\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| popl &lt;dbl&gt; |\n",
       "|---|\n",
       "| 6251750 |\n",
       "\n"
      ],
      "text/plain": [
       "  popl   \n",
       "1 6251750"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(name %in% c('Estonia', 'Latvia', 'Lithuania')) %>%\n",
    "    summarise(popl=sum(population))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Counting the countries of each continent\n",
    "\n",
    "For each **continent** show the **continent** and number of countries.\n",
    "\n",
    "> _Using GROUP BY and HAVING_   \n",
    "> You may want to look at these examples: [Using GROUP BY and HAVING](https://sqlzoo.net/wiki/Using_GROUP_BY_and_HAVING.)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 8 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>continent</th><th scope=col>n</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Africa       </td><td>53</td></tr>\n",
       "\t<tr><td>Asia         </td><td>47</td></tr>\n",
       "\t<tr><td>Caribbean    </td><td>11</td></tr>\n",
       "\t<tr><td>Eurasia      </td><td> 2</td></tr>\n",
       "\t<tr><td>Europe       </td><td>44</td></tr>\n",
       "\t<tr><td>North America</td><td>11</td></tr>\n",
       "\t<tr><td>Oceania      </td><td>14</td></tr>\n",
       "\t<tr><td>South America</td><td>13</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 8 × 2\n",
       "\\begin{tabular}{ll}\n",
       " continent & n\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Africa        & 53\\\\\n",
       "\t Asia          & 47\\\\\n",
       "\t Caribbean     & 11\\\\\n",
       "\t Eurasia       &  2\\\\\n",
       "\t Europe        & 44\\\\\n",
       "\t North America & 11\\\\\n",
       "\t Oceania       & 14\\\\\n",
       "\t South America & 13\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 8 × 2\n",
       "\n",
       "| continent &lt;chr&gt; | n &lt;int&gt; |\n",
       "|---|---|\n",
       "| Africa        | 53 |\n",
       "| Asia          | 47 |\n",
       "| Caribbean     | 11 |\n",
       "| Eurasia       |  2 |\n",
       "| Europe        | 44 |\n",
       "| North America | 11 |\n",
       "| Oceania       | 14 |\n",
       "| South America | 13 |\n",
       "\n"
      ],
      "text/plain": [
       "  continent     n \n",
       "1 Africa        53\n",
       "2 Asia          47\n",
       "3 Caribbean     11\n",
       "4 Eurasia        2\n",
       "5 Europe        44\n",
       "6 North America 11\n",
       "7 Oceania       14\n",
       "8 South America 13"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    group_by(continent) %>%\n",
    "    summarise(n=n())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Counting big countries in each continent\n",
    "\n",
    "For each **continent** show the **continent** and number of countries with populations of at least 10 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 8 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>continent</th><th scope=col>n</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Africa       </td><td>29</td></tr>\n",
       "\t<tr><td>Asia         </td><td>26</td></tr>\n",
       "\t<tr><td>Caribbean    </td><td> 2</td></tr>\n",
       "\t<tr><td>Eurasia      </td><td> 1</td></tr>\n",
       "\t<tr><td>Europe       </td><td>14</td></tr>\n",
       "\t<tr><td>North America</td><td> 4</td></tr>\n",
       "\t<tr><td>Oceania      </td><td> 1</td></tr>\n",
       "\t<tr><td>South America</td><td> 8</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 8 × 2\n",
       "\\begin{tabular}{ll}\n",
       " continent & n\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Africa        & 29\\\\\n",
       "\t Asia          & 26\\\\\n",
       "\t Caribbean     &  2\\\\\n",
       "\t Eurasia       &  1\\\\\n",
       "\t Europe        & 14\\\\\n",
       "\t North America &  4\\\\\n",
       "\t Oceania       &  1\\\\\n",
       "\t South America &  8\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 8 × 2\n",
       "\n",
       "| continent &lt;chr&gt; | n &lt;int&gt; |\n",
       "|---|---|\n",
       "| Africa        | 29 |\n",
       "| Asia          | 26 |\n",
       "| Caribbean     |  2 |\n",
       "| Eurasia       |  1 |\n",
       "| Europe        | 14 |\n",
       "| North America |  4 |\n",
       "| Oceania       |  1 |\n",
       "| South America |  8 |\n",
       "\n"
      ],
      "text/plain": [
       "  continent     n \n",
       "1 Africa        29\n",
       "2 Asia          26\n",
       "3 Caribbean      2\n",
       "4 Eurasia        1\n",
       "5 Europe        14\n",
       "6 North America  4\n",
       "7 Oceania        1\n",
       "8 South America  8"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    group_by(continent) %>% \n",
    "    filter(population>=1e7) %>%\n",
    "    summarise(n=n())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Counting big continents\n",
    "\n",
    "List the continents that **have** a total population of at least 100 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 6 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>continent</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Africa       </td></tr>\n",
       "\t<tr><td>Asia         </td></tr>\n",
       "\t<tr><td>Eurasia      </td></tr>\n",
       "\t<tr><td>Europe       </td></tr>\n",
       "\t<tr><td>North America</td></tr>\n",
       "\t<tr><td>South America</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 1\n",
       "\\begin{tabular}{l}\n",
       " continent\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Africa       \\\\\n",
       "\t Asia         \\\\\n",
       "\t Eurasia      \\\\\n",
       "\t Europe       \\\\\n",
       "\t North America\\\\\n",
       "\t South America\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 1\n",
       "\n",
       "| continent &lt;chr&gt; |\n",
       "|---|\n",
       "| Africa        |\n",
       "| Asia          |\n",
       "| Eurasia       |\n",
       "| Europe        |\n",
       "| North America |\n",
       "| South America |\n",
       "\n"
      ],
      "text/plain": [
       "  continent    \n",
       "1 Africa       \n",
       "2 Asia         \n",
       "3 Eurasia      \n",
       "4 Europe       \n",
       "5 North America\n",
       "6 South America"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    group_by(continent) %>% \n",
    "    summarise(popl=sum(population)) %>% \n",
    "    filter(popl>=1e8) %>% \n",
    "    select(continent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
